const mysql = require('mysql2/promise');

async function testMerchants() {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'community_service_platform'
  });

  try {
    console.log('🔍 测试商家表...\n');

    // 检查表是否存在
    const [tables] = await connection.execute('SHOW TABLES LIKE "merchants"');
    if (tables.length === 0) {
      console.log('❌ merchants表不存在，正在创建...');
      
      // 创建表
      await connection.execute(`
        CREATE TABLE IF NOT EXISTS merchants (
          id INT AUTO_INCREMENT PRIMARY KEY,
          name VARCHAR(100) NOT NULL COMMENT '商家名称',
          category VARCHAR(50) NOT NULL COMMENT '商家分类',
          address VARCHAR(200) NOT NULL COMMENT '商家地址',
          phone VARCHAR(20) NOT NULL COMMENT '联系电话',
          business_hours VARCHAR(100) COMMENT '营业时间',
          description TEXT COMMENT '商家描述',
          image_url VARCHAR(255) COMMENT '商家图片URL',
          latitude DECIMAL(10, 8) COMMENT '纬度',
          longitude DECIMAL(11, 8) COMMENT '经度',
          rating DECIMAL(2, 1) DEFAULT 5.0 COMMENT '评分',
          review_count INT DEFAULT 0 COMMENT '评价数量',
          is_active BOOLEAN DEFAULT TRUE COMMENT '是否营业',
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商家表'
      `);
      
      console.log('✅ merchants表创建成功');
    } else {
      console.log('✅ merchants表已存在');
    }

    // 检查数据
    const [count] = await connection.execute('SELECT COUNT(*) as count FROM merchants');
    console.log(`📊 当前商家数量: ${count[0].count}`);

    if (count[0].count === 0) {
      console.log('📝 插入示例数据...');
      
      // 插入示例数据
      await connection.execute(`
        INSERT INTO merchants (name, category, address, phone, business_hours, image_url, latitude, longitude, rating, review_count) VALUES
        ('社区便利店', '便利店', '社区1号楼1层101室', '13800138001', '07:00-22:00', '/img/merchants/convenience.jpg', 39.9042, 116.4074, 4.5, 128),
        ('甜蜜蛋糕房', '蛋糕房', '社区2号楼1层201室', '13800138002', '08:00-20:00', '/img/merchants/cake.jpg', 39.9043, 116.4075, 4.8, 95),
        ('社区卫生站', '社区卫生站', '社区3号楼1层301室', '13800138003', '08:30-17:30', '/img/merchants/health.jpg', 39.9044, 116.4076, 4.7, 156),
        ('物业服务中心', '物业报修', '社区物业楼1层', '13800138004', '24小时', '/img/merchants/property.jpg', 39.9045, 116.4077, 4.3, 89),
        ('社区洗衣店', '洗衣店', '社区4号楼1层401室', '13800138005', '08:00-19:00', '/img/merchants/laundry.jpg', 39.9046, 116.4078, 4.6, 67),
        ('社区理发店', '理发店', '社区5号楼1层501室', '13800138006', '09:00-20:00', '/img/merchants/haircut.jpg', 39.9047, 116.4079, 4.4, 78),
        ('社区药店', '药店', '社区6号楼1层601室', '13800138007', '08:00-21:00', '/img/merchants/pharmacy.jpg', 39.9048, 116.4080, 4.5, 112),
        ('社区银行', '银行', '社区7号楼1层701室', '13800138008', '09:00-17:00', '/img/merchants/bank.jpg', 39.9049, 116.4081, 4.2, 45)
      `);
      
      console.log('✅ 示例数据插入成功');
    }

    // 显示数据
    const [merchants] = await connection.execute('SELECT * FROM merchants LIMIT 5');
    console.log('\n📋 商家数据示例:');
    merchants.forEach((merchant, index) => {
      console.log(`${index + 1}. ${merchant.name} (${merchant.category}) - ${merchant.phone}`);
    });

    // 检查分类
    const [categories] = await connection.execute('SELECT DISTINCT category, COUNT(*) as count FROM merchants GROUP BY category');
    console.log('\n📂 商家分类统计:');
    categories.forEach(cat => {
      console.log(`  ${cat.category}: ${cat.count}家`);
    });

  } catch (error) {
    console.error('❌ 测试失败:', error.message);
  } finally {
    await connection.end();
  }

  console.log('\n🎉 商家表测试完成！');
}

testMerchants().catch(console.error);
